package icu.daydream.util;

import icu.daydream.pojo.GoodsInfo;
import icu.daydream.pojo.GoodsMedia;
import icu.daydream.pojo.Item;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @ClassName ExcelUtil
 * @Description Excel表格操作工具
 * @Author 白日梦
 * @Date 2021-03-06 1:42
 * @Version 1.0.0
 */
public class ExcelUtil {


    public static void writeItems(List<Item> items, String path)  {

        XSSFWorkbook workbook = new XSSFWorkbook();
        System.out.println("["+StringUtils.getCurTime()+"]"+"*=*=*=*=*=*=*=*=*=开始写入*=*=*=*=*=*=*=*=*=");
        System.out.println("["+StringUtils.getCurTime()+"]"+"*=*=*=*=*=*=*=*=*=写入商品属性*=*=*=*=*=*=*=*=*=");
        writeItemInfos(workbook, items );
        System.out.println("["+StringUtils.getCurTime()+"]"+"*=*=*=*=*=*=*=*=*=写入商品图片*=*=*=*=*=*=*=*=*=");
        writeItemImages(workbook,items);

        System.out.println("["+StringUtils.getCurTime()+"]"+"*=*=*=*=*=*=*=*=*=写入完成*=*=*=*=*=*=*=*=*=");
        File file = new File(path);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(file);
            workbook.write(fos);
            System.out.println("["+StringUtils.getCurTime()+"]"+"*=*=*=*=*=*=*=*=*=生成文件完成*=*=*=*=*=*=*=*=*=");
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                fos.close();
            } catch (IOException e) {}
        }

    }
    private static void writeItemInfos(XSSFWorkbook workbook,List<Item> items){
        String[] head = new String[]{"商品编号","商品名","价格","商品地址"};
        head = StringUtils.concat(head,PropUtil.props);
        XSSFSheet sheet = workbook.createSheet("商品信息");

        Row row = sheet.createRow(0);
        for (int i = 0; i < head.length; i++) {
            row.createCell(i).setCellValue(head[i]);
        }

        for (int n = 0; n < items.size(); n++) {
            row = sheet.createRow(n+1);
            Item item = items.get(n);
            row.createCell(0).setCellValue(item.getSkuId());
            row.createCell(1).setCellValue(item.getTitle());
            row.createCell(2).setCellValue(item.getPrice());
            row.createCell(3).setCellValue(item.getUrl());
            Set<Map.Entry<String, String>> entries = item.getProperties().entrySet();
            int i = 0;
            for (Map.Entry<String, String> entry : entries) {
                row.createCell(i+4).setCellValue(entry.getValue());
            }


            System.out.println("["+StringUtils.getCurTime()+"]"+"第"+(n+1)+"条商品信息==>["+items.get(n).getSkuId()+"]["+items.get(n).getTitle()+"]["+items.get(n).getPrice()+"]["+items.get(n).getUrl()+"]");
        }
    }

    private static void writeItemImages(XSSFWorkbook workbook, List<Item> items){

        String[] head = new String[]{"商品编号","图片"};
        XSSFSheet sheet = workbook.createSheet("商品图片");
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue(head[0]);
        row.createCell(1).setCellValue(head[1]);

        for (int n = 0; n < items.size(); n++) {
            row = sheet.createRow(n+1);
            List<String> images = items.get(n).getImages();
            row.createCell(0).setCellValue(items.get(n).getSkuId());
            for (int i = 0; i < images.size(); i++) {
                row.createCell(i+1).setCellValue(images.get(i));
            }
            System.out.println("["+StringUtils.getCurTime()+"]"+"第"+(n+1)+"条图片信息==>["+items.get(n)+"]["+images+"]");
        }

    }

    //获取商品列表urls
    public static List<String> getJINGDONGUrlsFromExcel(String path) throws IOException, InvalidFormatException {
        XSSFWorkbook workbook = new XSSFWorkbook(new File(path));
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowNumber = sheet.getPhysicalNumberOfRows();
        List<String> list = new ArrayList<>();
        for (int n = 0,i=0;n < rowNumber; n++,i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null){
                n--; continue;
            }
            XSSFCell cell = row.getCell(0);
            if(cell == null) {
                n--; continue;
            }
            String value = cell.getStringCellValue();
            if(value!= null && !value.trim().equals("")){
                list.add(value);
            }

        }
        return list;
    }


    /**
     * 从excel表格中获取GoodsInfo数据集合
     * @param path
     * @return
     */
    public static List<GoodsInfo> getGoodsInfoFromExcel(String path) throws IOException, InvalidFormatException {
        XSSFWorkbook workbook = new XSSFWorkbook(new File(path));
        XSSFSheet goodsSheet = workbook.getSheet("商品信息");
        XSSFSheet imageSheet = workbook.getSheet("商品图片");
        //表格行数
        int goodsRowNumber = goodsSheet.getPhysicalNumberOfRows();
        int imageRowNumber = goodsSheet.getPhysicalNumberOfRows();
//        PrintUtil.printParams(goodsRowNumber,imageRowNumber);
        //创建商品集合
        List<GoodsInfo> goodsList = new ArrayList<>();
        //
        for (int n = 1;n < goodsRowNumber; n++) {
            XSSFRow goodsRow = goodsSheet.getRow(n);
            XSSFRow imageRow = imageSheet.getRow(n);
            if (goodsRow == null){ break; }

            String goodsId = ExcelUtil.getValidCellValue(goodsRow.getCell(0));//获取商品编号
            String goodsName = ExcelUtil.getValidCellValue(goodsRow.getCell(1));//获取商品编号
            String goodsPrice = ExcelUtil.getValidCellValue(goodsRow.getCell(2));//获取商品编号
            String goodsBrand = ExcelUtil.getValidCellValue(goodsRow.getCell(4));//获取商品编号
            String goodsType = ExcelUtil.getValidCellValue(goodsRow.getCell(5));//获取商品编号
//            PrintUtil.printParams(goodsId,goodsName,goodsPrice,goodsBrand,goodsType);

            int cols = imageRow.getPhysicalNumberOfCells();
            List<GoodsMedia> medias = new ArrayList<>();
            for (int j = 1; j < cols; j++) {
                GoodsMedia goodsMedia = new GoodsMedia(j,goodsId,1);
                String imageUrl  =ExcelUtil.getValidCellValue(imageRow.getCell(j));
                goodsMedia.setUrl(imageUrl);
                medias.add(goodsMedia);
            }
            GoodsInfo  goodsInfo = new GoodsInfo(goodsId,goodsName,goodsBrand,0,
                    Double.parseDouble(goodsPrice),Integer.parseInt(goodsType),medias);
            goodsInfo.setStockNumber(StringUtils.getRandomNumber(0,20));
            goodsList.add(goodsInfo);
        }


        return goodsList;
    }


    /**
     * 获取有效cell的值
     * @param cell
     * @return
     */
    private static String getValidCellValue(Cell cell){
        if (cell == null){
            return null;
        }
        //设置单元格类型
        cell.setCellType(CellType.STRING);
        String value = cell.getStringCellValue();
        if(value == null || value.trim().equals("")){
            return null;
        }
        return value;
    }

    public static void main(String[] args) throws IOException, InvalidFormatException {
        String path = "C:\\Users\\biao bioss\\Desktop\\personal_webseite\\graduation project\\goodsInfo.xlsx";
//        List<String> list = getJINGDONGUrlsFromExcel(path);

        List<GoodsInfo> goodsInfos = getGoodsInfoFromExcel(path);
        for (GoodsInfo goodsInfo : goodsInfos) {
            System.out.println(goodsInfo);
        }


    }

}
